package light.head.dao.el;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import light.head.framework.base.BaseDAO;
import light.head.model.el.TrainedReport;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

@Service
public class TrainedReportDAO extends BaseDAO<Integer, TrainedReport>{
	
	private final static String hql_findByClcAndTrc5 = "FROM TrainedReport tr WHERE tr.clc = ? AND tr.deleted = 0 AND trc = 5 ORDER BY tr.ct";
	private final static String hql_findByClcAndUc = "FROM TrainedReport tr WHERE tr.clc = ? AND tr.deleted = 0 AND trc = 5 And uc = ? ORDER BY tr.ct";
	
	public List<TrainedReport> findByClcAndTrc5(Integer clc){
		return findI(hql_findByClcAndTrc5, clc);
	}
	

	
	public List<TrainedReport> findByClcAndUc(Integer clc,Integer uc){
		return findI(hql_findByClcAndUc, clc,uc);
	}
	/**
	 * 根据编辑编号查询 题目百分比 返回一个JSON数组集合
	 */
	public List<String> findPercentByClc(Integer clc){
		if(clc == null){
			return null;
		}
		StringBuffer sb = new StringBuffer();
		List<Object> list = new ArrayList<Object>();
		sb.append("SELECT (COUNT(tr.answer)/(SELECT COUNT(*) FROM TrainedReport tr1 WHERE tr1.clc = ? AND tr1.trc = 1)) AS 'percent',tr.answer,tr.trc FROM TrainedReport tr ");
		list.add(clc);
		sb.append(" WHERE tr.clc = ? AND tr.trc IN (1,2,3,4) AND tr.deleted = 0 GROUP BY tr.trc, tr.answer ");
		list.add(clc);
		final String sql = sb.toString();
		System.out.println(sql);
		final List<Object> parmList = list;
		return this.getHibernateTemplate().execute(new HibernateCallback<List<String>>() {

			@SuppressWarnings("unchecked")
			@Override
			public List<String> doInHibernate(Session session) throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				for (int i = 0; i < parmList.size(); i++) {
					query.setParameter(i, parmList.get(i));
				}
				List<Object[]> resultSet = query.list();
				List<String> list = new ArrayList<String>();
				StringBuffer json1 = new StringBuffer();
				StringBuffer json2 = new StringBuffer();
				StringBuffer json3 = new StringBuffer();
				StringBuffer json4 = new StringBuffer();
				String name = "";
				if (resultSet != null) {
					for (Object[] row : resultSet) {
						Float percent = row[0] != null ? ((BigDecimal)row[0]).floatValue() : null;
						Byte answer = row[1] !=null ? (Byte)row[1] : null;
						Integer trc = row[2] != null ? (Integer)row[2] : null;
						switch(answer){
						    case 1 :
							    name = "非常满意";
							    break;
						    case 2 :
							    name = "满意";
							    break;
						    case 3 :
							    name = "一般";
							    break;
						    case 4 :
							    name = "不满意";
							    break;
						}
						if(trc == 1){
							json1.append("['"+ name +"'," + percent + "],");
						}else if(trc == 2){
							json2.append("['"+ name +"'," + percent + "],");
						}else if(trc == 3){
							json3.append("['"+ name +"'," + percent + "],");
						}else if(trc == 4){
							json4.append("['"+ name +"'," + percent + "],");
						}
					}
					if(json1.length() != 0){
						json1.deleteCharAt(json1.length() - 1);
						list.add(json1.toString());
					}
					if(json2.length() != 0){
						json2.deleteCharAt(json2.length() - 1);
						list.add(json2.toString());
					}
					if(json3.length() != 0){
						json3.deleteCharAt(json3.length() - 1);
						list.add(json3.toString());
					}
					if(json4.length() != 0){
						json4.deleteCharAt(json4.length() - 1);
						list.add(json4.toString());
					}
				}
				return list;
			}
		});
	}
}

